package cn.edu.gench.learn.dao;

import cn.edu.gench.learn.model.Book;
import cn.edu.gench.learn.util.ConnectFactory;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/4/28.
 */
@Service
public class BookDao {


    public List<Book> queryAll() {
            List<Book> cityModels = new ArrayList<Book>();
            try {
                // 加载驱动程序
                Connection conn = ConnectFactory.getConnection();
                Statement statement = conn.createStatement();
                // 要执行的SQL语句
                String querySql = "select * from d8_book";
                ResultSet rs = statement.executeQuery(querySql);
                while (rs.next()) {
                    Book book = new Book();
                    book.setName(rs.getString("name"));
                    book.setId(rs.getInt("id"));
                    book.setCreatedAt(rs.getDate("created_at"));
                    cityModels.add(book);
                }
                ConnectFactory.close(conn,rs,statement);
            } catch(SQLException e) {
                e.printStackTrace();
            }
            return cityModels;
        }

    public Book queryBookById(int id) {
         Book book = new Book();
        System.out.println(id);

//        try {
//            // 加载驱动程序
//            Connection conn = ConnectFactory.getConnection();
//            Statement statement = conn.createStatement();
//            // 要执行的SQL语句
//            String querySql = "select * from d8_book where id = " + id;
//
//            ResultSet rs = statement.executeQuery(querySql);
//            while (rs.next()) {
//                book.setName(rs.getString("name"));
//                book.setId(rs.getInt("id"));
//                book.setCreatedAt(rs.getDate("created_at"));
//            }
//            ConnectFactory.close(conn,rs,statement);
//        } catch(SQLException e) {
//            e.printStackTrace();
//        }
        return book;

    }

    public void updateNameById(int id, String name) {


        try {
            // 加载驱动程序
            Connection conn = ConnectFactory.getConnection();
            Statement statement = conn.createStatement();
            // 要执行的SQL语句
            String sql = "update d8_book set name ='"+name+"' where id = " + id;

            System.out.println("update result: " + statement.execute(sql));
            ConnectFactory.close(conn,null,statement);
        } catch(SQLException e) {
            e.printStackTrace();
        }


    }

    public void delete(int id) {
        try {
            // 加载驱动程序
            Connection conn = ConnectFactory.getConnection();
            Statement statement = conn.createStatement();
            // 要执行的SQL语句
            String sql = "delete from d8_book where id = " + id;

            System.out.println("delete result: " + statement.execute(sql));
            ConnectFactory.close(conn,null,statement);
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }

    public String test(int id, String name) {
        System.out.println(MessageFormat.format("dao=> id = {0} ,name = {1}",id,name));
        return null;
    }


























}
